<?php
include_once 'MY/extra_sql.php';
include_once 'PHPExcel.php';
include_once 'PHPExcel/IOFactory.php';
class ExportController extends Zend_Controller_Action{
	function init(){
		$this->registry = Zend_Registry::getInstance();
		$this->view = $this->registry['view'];
		$this->view->baseUrl = $this->_request->getBaseUrl();
		$this->view->template = "system";
	}

	public function indexAction(){
            
       echo $this->view->render('export/index.html');
	}

	
	public function exportAction(){
		
	    $filter=new Zend_Filter_StripTags();
	    $db = new mysql(constant('dbhost'),constant('dbuser'),constant('dbpass'),constant('dbname'),"UTF8");
	    
	    $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
	    $objPHPExcel = new PHPExcel();
		$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
		->setLastModifiedBy("Maarten Balliauw")
		->setTitle("Office 2007 XLSX Test Document")
		->setSubject("Office 2007 XLSX Test Document")
		->setDescription("Document for Office 2007 XLSX, generated using PHP classes.")
		->setKeywords("office 2007 openxml php")
		->setCategory("Test result file");

//	    $model=$filter->filter($this->_request->getParam('model'));
//	    $fields = $db->describe_name($model);
                
//		$objPHPExcel->setActiveSheetIndex(0);
//		$objRichText = new PHPExcel_RichText();
//		$objRichText->createText('');
//		$objPayable = $objRichText->createTextRun($model.'模板');
//		
//        foreach($fields as $key=>$val){
//           $cess = $letter[$key]."1";
//           $objPHPExcel->getActiveSheet()->setCellValue($cess,$val[0]);
//        }
//        
//		$objPHPExcel->setActiveSheetIndex(0);
//		$objPHPExcel->getActiveSheet()->setTitle($model);
		
	        //取得所有的项目	    
		    $project = new Project();
	        $projects = $project->getAllProjectsexcel();
			//添加所有项目
			$objPHPExcel->createSheet(); 
			$objPHPExcel->getSheet(0)->setTitle('project'); 
			$objPHPExcel->setActiveSheetIndex(0);
			$objRichText = new PHPExcel_RichText();
			$objRichText->createText('');
	        foreach($projects as $key=>$val){
	           $cess = "A".($key+1);
	           $cell = "B".($key+1);
	           $objPHPExcel->getActiveSheet()->setCellValue($cess,$val['title']);
	           $objPHPExcel->getActiveSheet()->setCellValue($cell,$val['project_id']);
	        }
	        $objPHPExcel->setActiveSheetIndex(0);
	        
	        //取得所有的教师	    
		    $teacher = new Teacher();
	        $teachers = $db->select_teachers('','','');
			//添加所有教师 
			$objPHPExcel->createSheet(); 
			$objPHPExcel->getSheet(1)->setTitle('teacher'); 
			$objPHPExcel->setActiveSheetIndex(1);
			$objRichText = new PHPExcel_RichText();
			$objRichText->createText('');
	        foreach($teachers as $key=>$val){
	           $cess = "A".($key+1);
	           $cell = "B".($key+1);
	           $objPHPExcel->getActiveSheet()->setCellValue($cess,$val['name']);
	           $objPHPExcel->getActiveSheet()->setCellValue($cell,$val['teacher_id']);           
	        }
	        $objPHPExcel->setActiveSheetIndex(1);
	        
	        
	        //取得所有班级        
	        $clss = new Classes();
	        $classes = $clss->getAllClassesexcel();
	        //添加所有班级 
			$objPHPExcel->createSheet(); 
			$objPHPExcel->getSheet(2)->setTitle('class'); 
			$objPHPExcel->setActiveSheetIndex(2);
			$objRichText = new PHPExcel_RichText();
			$objRichText->createText('');
	        foreach($classes as $key=>$val){
	           $cess = "A".($key+1);
	           $cell = "B".($key+1);
	           $objPHPExcel->getActiveSheet()->setCellValue($cess,$val['name']);
	           $objPHPExcel->getActiveSheet()->setCellValue($cell,$val['class_id']);       
	        }
	        $objPHPExcel->setActiveSheetIndex(2);
	        
	        
	        //取得所有学生
	        $student = new Student();
	        $students = $student->getAllStudentsexcel();
	        //添加所有班级 
			$objPHPExcel->createSheet(); 
			$objPHPExcel->getSheet(3)->setTitle('student'); 
			$objPHPExcel->setActiveSheetIndex(3);
			$objRichText = new PHPExcel_RichText();
			$objRichText->createText('');
	        foreach($students as $key=>$val){
	           $cess = "A".($key+1);
	           $cell = "B".($key+1);
	           $objPHPExcel->getActiveSheet()->setCellValue($cess,$val['name']);
	           $objPHPExcel->getActiveSheet()->setCellValue($cell,$val['student_id']); 
	        }
	        $objPHPExcel->setActiveSheetIndex(3);

	        
	        //取得所有公司        
	        $company = new Company();
	        $companys = $company->getAllCompanysexcel();
	        //添加所有公司 
			$objPHPExcel->createSheet(); 
			$objPHPExcel->getSheet(4)->setTitle('company'); 
			$objPHPExcel->setActiveSheetIndex(4);
			$objRichText = new PHPExcel_RichText();
			$objRichText->createText('');
	        foreach($companys as $key=>$val){
	           $cess = "A".($key+1);
	           $cell = "B".($key+1);
	           $objPHPExcel->getActiveSheet()->setCellValue($cess,$val['name']);
	           $objPHPExcel->getActiveSheet()->setCellValue($cell,$val['company_id']);
	        }
	        $objPHPExcel->setActiveSheetIndex(4);
	        
	  //取得所有产品        
	        $product = new Product();
	        $products = $product->getAllProductsexcel();
	        //添加所有产品 
			$objPHPExcel->createSheet(); 
			$objPHPExcel->getSheet(5)->setTitle('product'); 
			$objPHPExcel->setActiveSheetIndex(5);
			$objRichText = new PHPExcel_RichText();
			$objRichText->createText('');
	        foreach($products as $key=>$val){
	           $cess = "A".($key+1);
	           $cell = "B".($key+1);
	           $objPHPExcel->getActiveSheet()->setCellValue($cess,$val['name']);
	           $objPHPExcel->getActiveSheet()->setCellValue($cell,$val['product_id']);
	        }
	        $objPHPExcel->setActiveSheetIndex(5);
	             
	        //取得所有专业        
	        $department = new Department();
	        $departments = $department->getAlldepartmentsexcel();
	        //添加所有专业
			$objPHPExcel->createSheet(); 
			$objPHPExcel->getSheet(6)->setTitle('department'); 
			$objPHPExcel->setActiveSheetIndex(6);
			$objRichText = new PHPExcel_RichText();
			$objRichText->createText('');
	        foreach($departments as $key=>$val){
	           $cess = "A".($key+1);
	           $cell = "B".($key+1);
	           $objPHPExcel->getActiveSheet()->setCellValue($cess,$val['name']);
	           $objPHPExcel->getActiveSheet()->setCellValue($cell,$val['id']);    
	        }
	        $objPHPExcel->setActiveSheetIndex(6);
	                
	        //取得所有用户       
	        $user = new User();
	        $users = $user->getAllUsersexcel();
	        //添加所有用户 
			$objPHPExcel->createSheet(); 
			$objPHPExcel->getSheet(7)->setTitle('user'); 
			$objPHPExcel->setActiveSheetIndex(7);
			$objRichText = new PHPExcel_RichText();
			$objRichText->createText('');
	        foreach($users as $key=>$val){
	           $cess = "A".($key+1);
	           $cell = "B".($key+1);
	           $objPHPExcel->getActiveSheet()->setCellValue($cess,$val['user_name']);
	           $objPHPExcel->getActiveSheet()->setCellValue($cell,$val['user_id']);
	        }
	        $objPHPExcel->setActiveSheetIndex(7);
	        
	         //取得所有用户       
	        $course = new Course();
	        $courses = $course->getAllCoursesexcel();
	        //添加所有用户 
			$objPHPExcel->createSheet(); 
			$objPHPExcel->getSheet(8)->setTitle('course'); 
			$objPHPExcel->setActiveSheetIndex(8);
			$objRichText = new PHPExcel_RichText();
			$objRichText->createText('');
	        foreach($courses as $key=>$val){
	           $cess = "A".($key+1);
	           $cell = "B".($key+1);
	           $objPHPExcel->getActiveSheet()->setCellValue($cess,$val['name']);
	           $objPHPExcel->getActiveSheet()->setCellValue($cell,$val['course_id']);
	        }
	        $objPHPExcel->setActiveSheetIndex(8);
   
        
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$name = 'export'.'.xls';
		$name = iconv('utf-8','gb2312',$name);
		$objWriter->save("public/exports/".$name);
		$this->view->attachment = $name;
		
		echo $this->view->render('export/export.html');
	}
}	